package com.fy.utils;

import com.fy.config.FileConfig;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xddf.usermodel.PresetColor;
import org.apache.poi.xddf.usermodel.XDDFColor;
import org.apache.poi.xddf.usermodel.XDDFSolidFillProperties;
import org.apache.poi.xddf.usermodel.chart.*;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTDLbls;
import org.openxmlformats.schemas.drawingml.x2006.chart.STDLblPos;

import java.io.*;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 *
 * Excel 工具类
 *
 *
 */
public class ExcelUtil {
    private Workbook workbook;

    private String pattern = "yyyy-MM-dd HH:mm:ss";




    public ExcelUtil(Workbook workboook) {
        this.workbook = workboook;
    }

    public ExcelUtil(InputStream is, String version) throws FileNotFoundException, IOException {
        if ("2003".equals(version)) {
            workbook = new HSSFWorkbook(is);
        } else {
            workbook = new XSSFWorkbook(is);
        }
    }

    /**
     *
     * 读取指定sheet 页所有数据
     *
     * @param sheetIx
     *            指定 sheet 页，从 0 开始
     * @return
     * @throws Exception
     */
    public List<List<String>> read(int sheetIx) throws Exception {
        return read(sheetIx, 0, getRowCount(sheetIx) - 1);
    }

    /**
     *
     * 读取指定sheet 页指定行数据
     *
     * @param sheetIx
     *            指定 sheet 页，从 0 开始
     * @param start
     *            指定开始行，从 0 开始
     * @param end
     *            指定结束行，从 0 开始
     * @return
     * @throws Exception
     */
    public List<List<String>> read(int sheetIx, int start, int end) throws Exception {
        Sheet sheet = workbook.getSheetAt(sheetIx);
        List<List<String>> list = new ArrayList<List<String>>();

        if (end > getRowCount(sheetIx)) {
            end = getRowCount(sheetIx);
        }

        int cols = sheet.getRow(0).getLastCellNum(); // 第一行总列数

        for (int i = start; i <= end; i++) {
            List<String> rowList = new ArrayList<String>();
            Row row = sheet.getRow(i);
            for (int j = 0; j < cols; j++) {
                if (row == null) {
                    rowList.add(null);
                    continue;
                }
                rowList.add(getCellValueToString(row.getCell(j)));
            }
            list.add(rowList);
        }

        return list;
    }

    /**
     *
     * 将数据写入到 Excel 新创建的 Sheet 页
     *
     * @param rowData
     *            数据
     * @param sheetName
     *            长度为1-31，不能包含后面任一字符: ：\ / ? * [ ]
     * @return
     * @throws IOException
     */
    public boolean write(List<String[]> rowData, String sheetName, boolean isNewSheet) throws IOException {
        Sheet sheet = null;
        if (isNewSheet) {
            sheet = workbook.createSheet(sheetName);
        } else {
            sheet = workbook.createSheet();
        }
        int sheetIx = workbook.getSheetIndex(sheet);
        return write(sheetIx, rowData, 0);
    }

    /**
     *
     * 将数据追加到sheet页最后
     *
     * @param rowData
     *            数据
     * @param sheetIx
     *            指定 Sheet 页，从 0 开始
     * @param isAppend
     *            是否追加,true 追加，false 重置sheet再添加
     * @return
     * @throws IOException
     */
    public boolean write(int sheetIx, List<String[]> rowData, boolean isAppend) throws IOException {
        if (isAppend) {
            return write(sheetIx, rowData, getRowCount(sheetIx));
        } else {// 清空再添加
            clearSheet(sheetIx);
            return write(sheetIx, rowData, 0);
        }
    }

    /**
     *
     * 将数据写入到 Excel 指定 Sheet 页指定开始行中,指定行后面数据向后移动
     *
     * @param rowData
     *            数据
     * @param sheetIx
     *            指定 Sheet 页，从 0 开始
     * @param startRow
     *            指定开始行，从 0 开始
     * @return
     * @throws IOException
     */
    public boolean write(int sheetIx, List<String[]> rowData, int startRow) throws IOException {
        Sheet sheet = workbook.getSheetAt(sheetIx);
        int dataSize = rowData.size();
        if (getRowCount(sheetIx) > 0) {// 如果小于等于0，则一行都不存在
            sheet.shiftRows(startRow, getRowCount(sheetIx), dataSize);
        }
        for (int i = 0; i < dataSize; i++) {
            Row row = sheet.createRow(i + startRow);
            for (int j = 0; j < rowData.get(i).length; j++) {
                Cell cell = row.createCell(j);
                cell.setCellValue(rowData.get(i)[j] + "");
                //默认样式
                cell.setCellStyle(getColumnStyle(11,false,HorizontalAlignment.LEFT,false));
            }
        }
        return true;
    }


    /**
     *
     * 合并单元格
     *
     * @param sheetIx
     *            指定 Sheet 页，从 0 开始
     * @param firstRow
     *            开始行
     * @param lastRow
     *            结束行
     * @param firstCol
     *            开始列
     * @param lastCol
     *            结束列
     */
    public void region(int sheetIx, int firstRow, int lastRow, int firstCol, int lastCol) {
        Sheet sheet = workbook.getSheetAt(sheetIx);
        sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
    }


    /**
     *
     * 创建行，若行存在，则清空
     *
     * @param sheetIx
     *            指定 sheet 页，从 0 开始
     * @param rowIndex
     *            指定创建行，从 0 开始
     * @return
     * @throws IOException
     */
    public boolean createRow(int sheetIx, int rowIndex) throws IOException {
        Sheet sheet = workbook.getSheetAt(sheetIx);
        sheet.createRow(rowIndex);
        return true;
    }



    /**
     *
     * 创建单元格
     *
     * @param sheetIx
     *            指定 sheet 页，从 0 开始
     * @param rowIndex
     *            指定行，从 0 开始
     * @param colIndex
     *            指定创建列，从 0 开始
     * @return true 列为空，false 行不为空
     * @throws IOException
     */
    public boolean createCell(int sheetIx, int rowIndex, int colIndex) throws IOException {
        Sheet sheet = workbook.getSheetAt(sheetIx);
        Row row = sheet.getRow(rowIndex);
        row.createCell(colIndex);
        return true;
    }

    /**
     * 返回sheet 中的行数
     *
     *
     * @param sheetIx
     *            指定 Sheet 页，从 0 开始
     * @return
     */
    public int getRowCount(int sheetIx) {
        Sheet sheet = workbook.getSheetAt(sheetIx);
        if (sheet.getPhysicalNumberOfRows() == 0) {
            return 0;
        }
        return sheet.getLastRowNum() + 1;

    }

    /**
     *
     * 返回所在行的列数
     *
     * @param sheetIx
     *            指定 Sheet 页，从 0 开始
     * @param rowIndex
     *            指定行，从0开始
     * @return 返回-1 表示所在行为空
     */
    public int getColumnCount(int sheetIx, int rowIndex) {
        Sheet sheet = workbook.getSheetAt(sheetIx);
        Row row = sheet.getRow(rowIndex);
        return row == null ? -1 : row.getLastCellNum();

    }

    /**
     *
     * 设置row 和 column 位置的单元格值
     *
     * @param sheetIx
     *            指定 Sheet 页，从 0 开始
     * @param rowIndex
     *            指定行，从0开始
     * @param colIndex
     *            指定列，从0开始
     * @param value
     *            值
     * @return
     * @throws IOException
     */
    public boolean setValueAt(int sheetIx, int rowIndex, int colIndex, String value) throws IOException {
        Sheet sheet = workbook.getSheetAt(sheetIx);
        sheet.getRow(rowIndex).getCell(colIndex).setCellValue(value);
        return true;
    }

    /**
     *
     * 返回 row 和 column 位置的单元格值
     *
     * @param sheetIx
     *            指定 Sheet 页，从 0 开始
     * @param rowIndex
     *            指定行，从0开始
     * @param colIndex
     *            指定列，从0开始
     * @return
     *
     */
    public String getValueAt(int sheetIx, int rowIndex, int colIndex) {
        Sheet sheet = workbook.getSheetAt(sheetIx);
        return getCellValueToString(sheet.getRow(rowIndex).getCell(colIndex));
    }

    /**
     *
     * 重置指定行的值
     *
     * @param rowData
     *            数据
     * @param sheetIx
     *            指定 Sheet 页，从 0 开始
     * @param rowIndex
     *            指定行，从0开始
     * @return
     * @throws IOException
     */
    public boolean setRowValue(int sheetIx, List<String> rowData, int rowIndex) throws IOException {
        Sheet sheet = workbook.getSheetAt(sheetIx);
        Row row = sheet.getRow(rowIndex);
        for (int i = 0; i < rowData.size(); i++) {
            row.getCell(i).setCellValue(rowData.get(i));
        }
        return true;
    }

    /**
     *
     * 返回指定行的值的集合
     *
     * @param sheetIx
     *            指定 Sheet 页，从 0 开始
     * @param rowIndex
     *            指定行，从0开始
     * @return
     */
    public List<String> getRowValue(int sheetIx, int rowIndex) {
        Sheet sheet = workbook.getSheetAt(sheetIx);
        Row row = sheet.getRow(rowIndex);
        List<String> list = new ArrayList<String>();
        if (row == null) {
            list.add(null);
        } else {
            for (int i = 0; i < row.getLastCellNum(); i++) {
                list.add(getCellValueToString(row.getCell(i)));
            }
        }
        return list;
    }

    /**
     *
     * 返回列的值的集合
     *
     * @param sheetIx
     *            指定 Sheet 页，从 0 开始
     * @param rowIndex
     *            指定行，从0开始
     * @param colIndex
     *            指定列，从0开始
     * @return
     */
    public List<String> getColumnValue(int sheetIx, int rowIndex, int colIndex) {
        Sheet sheet = workbook.getSheetAt(sheetIx);
        List<String> list = new ArrayList<String>();
        for (int i = rowIndex; i < getRowCount(sheetIx); i++) {
            Row row = sheet.getRow(i);
            if (row == null) {
                list.add(null);
                continue;
            }
            list.add(getCellValueToString(sheet.getRow(i).getCell(colIndex)));
        }
        return list;
    }

    /**
     *
     * 获取excel 中sheet 总页数
     *
     * @return
     */
    public int getSheetCount() {
        return workbook.getNumberOfSheets();
    }

    public void createSheet() {
        workbook.createSheet();
    }

    /**
     *
     * 设置sheet名称，长度为1-31，不能包含后面任一字符: ：\ / ? * [ ]
     *
     * @param sheetIx
     *            指定 Sheet 页，从 0 开始，//
     * @param name
     * @return
     * @throws IOException
     */
    public boolean setSheetName(int sheetIx, String name) throws IOException {
        workbook.setSheetName(sheetIx, name);
        return true;
    }

    /**
     *
     * 获取 sheet名称
     *
     * @param sheetIx
     *            指定 Sheet 页，从 0 开始
     * @return
     * @throws IOException
     */
    public String getSheetName(int sheetIx) throws IOException {
        Sheet sheet = workbook.getSheetAt(sheetIx);
        return sheet.getSheetName();
    }

    /**
     * 获取sheet的索引，从0开始
     *
     * @param name
     *            sheet 名称
     * @return -1表示该未找到名称对应的sheet
     */
    public int getSheetIndex(String name) {
        return workbook.getSheetIndex(name);
    }

    /**
     *
     * 删除指定sheet
     *
     * @param sheetIx
     *            指定 Sheet 页，从 0 开始
     * @return
     * @throws IOException
     */
    public boolean removeSheetAt(int sheetIx) throws IOException {
        workbook.removeSheetAt(sheetIx);
        return true;
    }

    /**
     *
     * 删除指定sheet中行，改变该行之后行的索引
     *
     * @param sheetIx
     *            指定 Sheet 页，从 0 开始
     * @param rowIndex
     *            指定行，从0开始
     * @return
     * @throws IOException
     */
    public boolean removeRow(int sheetIx, int rowIndex) throws IOException {
        Sheet sheet = workbook.getSheetAt(sheetIx);
        sheet.shiftRows(rowIndex + 1, getRowCount(sheetIx), -1);
        Row row = sheet.getRow(getRowCount(sheetIx) - 1);
        sheet.removeRow(row);
        return true;
    }

    /**
     *
     * 设置sheet 页的索引
     *
     * @param sheetname
     *            Sheet 名称
     * @param sheetIx
     *            Sheet 索引，从0开始
     */
    public void setSheetOrder(String sheetname, int sheetIx) {
        workbook.setSheetOrder(sheetname, sheetIx);
    }

    /**
     *
     * 清空指定sheet页（先删除后添加并指定sheetIx）
     *
     * @param sheetIx
     *            指定 Sheet 页，从 0 开始
     * @return
     * @throws IOException
     */
    public boolean clearSheet(int sheetIx) throws IOException {
        String sheetname = getSheetName(sheetIx);
        removeSheetAt(sheetIx);
        workbook.createSheet(sheetname);
        setSheetOrder(sheetname, sheetIx);
        return true;
    }

    public Workbook getWorkbook() {
        return workbook;
    }



    /**
     *
     * 转换单元格的类型为String 默认的 <br>
     * 默认的数据类型：CELL_TYPE_BLANK(3), CELL_TYPE_BOOLEAN(4),
     * CELL_TYPE_ERROR(5),CELL_TYPE_FORMULA(2), CELL_TYPE_NUMERIC(0),
     * CELL_TYPE_STRING(1)
     *
     * @param cell
     * @return
     *
     */
    private String getCellValueToString(Cell cell) {
        String strCell = "";
        if (cell == null) {
            return null;
        }
        switch (cell.getCellType()) {
            case BOOLEAN:
                strCell = String.valueOf(cell.getBooleanCellValue());
                break;
            case NUMERIC:
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    Date date = cell.getDateCellValue();
                    if (pattern != null) {
                        SimpleDateFormat sdf = new SimpleDateFormat(pattern);
                        strCell = sdf.format(date);
                    } else {
                        strCell = date.toString();
                    }
                    break;
                }
                // 不是日期格式，则防止当数字过长时以科学计数法显示
                cell.setCellType(CellType.STRING);
                strCell = cell.toString();
                break;
            case STRING:
                strCell = cell.getStringCellValue();
                break;
            default:
                break;
        }
        return strCell;
    }



    public CellStyle getColumnStyle(int fontSize,boolean bold,HorizontalAlignment horizontalAlignment,boolean warpText) {
        // 设置字体
        Font font = workbook.createFont();
        //设置字体大小
        font.setFontHeightInPoints((short)fontSize);
        //字体加粗
        font.setBold(bold);
        //设置字体名字
        font.setFontName("宋体");
        //设置样式;
        CellStyle style = workbook.createCellStyle();
        //在样式用应用设置的字体;
        style.setFont(font);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(horizontalAlignment);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setWrapText(warpText);
        return style;

    }


    private  XSSFChart createDrawingPatriarch(XSSFSheet sheet,String chartTitle, int col1, int col2,int row1, int row2) {
        //创建一个画布
        XSSFDrawing drawing = sheet.createDrawingPatriarch();
        //前偏移量四个默认0
        XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, col1,row1, col2, row2);
        //创建一个chart对象
        XSSFChart chart = drawing.createChart(anchor);
        //标题
        chart.setTitleText(chartTitle);
        //标题是否覆盖图表
        chart.setTitleOverlay(false);
        return chart;
    }


    public  void createPie(XSSFSheet sheet, String[] titles, BigDecimal datas[],  String titleName, int col1, int col2,int row1, int row2) {
        XSSFChart chart = createDrawingPatriarch(sheet, titleName, col1,row1, col2, row2);
        //图例位置
        XDDFChartLegend legend = chart.getOrAddLegend();
        legend.setPosition(LegendPosition.LEFT);

        //CellRangeAddress(起始行号，终止行号， 起始列号，终止列号）
        //分类轴标数据，
        XDDFDataSource<String> countries = XDDFDataSourcesFactory.fromArray(titles);
        XDDFNumericalDataSource<BigDecimal> values = XDDFDataSourcesFactory.fromArray(datas);
        XDDFChartData data = chart.createData(ChartTypes.PIE, null, null);
        //设置为可变颜色
        data.setVaryColors(true);
        //图表加载数据
        data.addSeries(countries, values);

        //绘制
        chart.plot(data);
        CTDLbls ctdLbls = chart.getCTChart().getPlotArea().getPieChartArray(0).getSerArray(0).addNewDLbls();
        ctdLbls.addNewShowVal().setVal(false);
        ctdLbls.addNewShowLegendKey().setVal(false);
        //类别名称
        ctdLbls.addNewShowCatName().setVal(true);
        //百分比
        ctdLbls.addNewShowSerName().setVal(false);
        ctdLbls.addNewShowPercent().setVal(true);
        //引导线
        ctdLbls.addNewShowLeaderLines().setVal(true);
        //分隔符为分行符
        ctdLbls.setSeparator("\n");
        //数据标签内
        ctdLbls.addNewDLblPos().setVal(STDLblPos.Enum.forString("inEnd"));
    }


    public  void createBar(XSSFSheet sheet, String[] titles, BigDecimal datas[],  String titleName, int col1, int col2,int row1, int row2) {
        XSSFChart chart = createDrawingPatriarch(sheet, titleName, col1,row1, col2, row2);
        //图例位置
        XDDFChartLegend legend = chart.getOrAddLegend();
        legend.setPosition(LegendPosition.LEFT);

        //CellRangeAddress(起始行号，终止行号， 起始列号，终止列号）
        //分类轴标数据，
        XDDFDataSource<String> countries = XDDFDataSourcesFactory.fromArray(titles);
        XDDFNumericalDataSource<BigDecimal> values = XDDFDataSourcesFactory.fromArray(datas);
        XDDFChartData data = chart.createData(ChartTypes.BAR, null, null);
        //设置为可变颜色
        data.setVaryColors(true);
        //图表加载数据
        data.addSeries(countries, values);

        //绘制
        chart.plot(data);
        CTDLbls ctdLbls = chart.getCTChart().getPlotArea().getPieChartArray(0).getSerArray(0).addNewDLbls();
        ctdLbls.addNewShowVal().setVal(false);
        ctdLbls.addNewShowLegendKey().setVal(false);
        //类别名称
        ctdLbls.addNewShowCatName().setVal(true);
        //百分比
        ctdLbls.addNewShowSerName().setVal(false);
        ctdLbls.addNewShowPercent().setVal(true);
        //引导线
        ctdLbls.addNewShowLeaderLines().setVal(true);
        //分隔符为分行符
        ctdLbls.setSeparator("\n");
        //数据标签内
        ctdLbls.addNewDLblPos().setVal(STDLblPos.Enum.forString("inEnd"));



    }

    public static void createBar(XSSFSheet sheet, String title, String xTitle,String yTitle,String makeTitle, String[] xData, BigDecimal[] yData,int col1, int col2,int row1, int row2){
        try{
            XSSFDrawing drawing = sheet.createDrawingPatriarch();
            // 前四个默认0，[0,5]：从0列5行开始;[7,26]:到7列26行结束
            // 默认宽度(14-8)*12
            XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, col1, row1, col2, row2);
            // 创建一个chart对象
            XSSFChart chart = drawing.createChart(anchor);
            // 标题
            chart.setTitleText(title);
            // 标题覆盖
            chart.setTitleOverlay(false);

            // 图例位置
            XDDFChartLegend legend = chart.getOrAddLegend();
            legend.setPosition(LegendPosition.TOP);

            // 分类轴标(X轴),标题位置
            XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
            bottomAxis.setTitle(xTitle);
            // 值(Y轴)轴,标题位置
            XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);
            leftAxis.setTitle(yTitle);
            XDDFCategoryDataSource countries = XDDFDataSourcesFactory.fromArray(xData);
            XDDFNumericalDataSource<BigDecimal> area =  XDDFDataSourcesFactory.fromArray(yData);
            // bar：条形图，
            XDDFBarChartData bar = (XDDFBarChartData) chart.createData(ChartTypes.BAR, bottomAxis, leftAxis);

            leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);
            leftAxis.setCrossBetween(AxisCrossBetween.BETWEEN);
            //设置为可变颜色
            bar.setVaryColors(false);
            // 条形图方向，纵向/横向：纵向
            //bar.setBarDirection(BarDirection.COL);
            bar.setBarDirection(BarDirection.BAR);

            // 图表加载数据，条形图1
            XDDFBarChartData.Series series1 = (XDDFBarChartData.Series) bar.addSeries(countries, area);
            // 条形图例标题
            series1.setTitle(makeTitle, null);
            XDDFSolidFillProperties fill = new XDDFSolidFillProperties(XDDFColor.from(PresetColor.BLUE));
            // 条形图，填充颜色
            series1.setFillProperties(fill);

            // 绘制
            chart.plot(bar);
        }catch (Exception e){
            e.printStackTrace();
        }
    }


    public void setExcelSytle(List<Map<String, Object>> resList) {
        XSSFSheet sheet = (XSSFSheet) workbook.getSheetAt(0);
        for (int i = 0; i <resList.size() ; i++) {
            Map<String,Object> map = resList.get(i);
            for (String key:map.keySet()) {
                if(key.equals("KUALIE")){
                    String[] array = map.get(key).toString().split("-");
                    region(0,Integer.parseInt(array[0]), Integer.parseInt(array[0]), Integer.parseInt(array[1]), Integer.parseInt(array[2]));
                    sheet.getRow(Integer.parseInt(array[0])).setHeight((short)600);
                    sheet.getRow(Integer.parseInt(array[0])).getCell(0).setCellStyle(getColumnStyle(12,true,HorizontalAlignment.CENTER,false));
                }else if(key.indexOf("KUALIE") > -1){
                    String[] array = map.get(key).toString().split("-");
                    region(0,Integer.parseInt(array[0]), Integer.parseInt(array[0]), Integer.parseInt(array[1]), Integer.parseInt(array[2]));
                    sheet.getRow(Integer.parseInt(array[0])).getCell(0).setCellStyle(getColumnStyle(11,false,HorizontalAlignment.LEFT,false));
                }
                if (key.equals("TB-PIE")){
                    Map<String, Object> cmap = (Map<String, Object>) map.get(key);
                    String titleName = cmap.get("titleName").toString();
                    String[] titles = (String[]) cmap.get("titles");
                    BigDecimal[] datas = (BigDecimal[]) cmap.get("datas");
                    createPie(sheet,titles,datas ,titleName,0,sheet.getLastRowNum() + 2,8,sheet.getLastRowNum() + 20);
                }
            }
        }

        int cellCount = getColumnCount(0,sheet.getLastRowNum()-1);
        for (int i = 0; i <cellCount ; i++) {
            Cell cell = sheet.getRow(sheet.getLastRowNum()-1).getCell(i);
            //int colLength = cell.getStringCellValue().getBytes().length*400;
            sheet.setColumnWidth(i, 2400);
        }
    }


    public void  setColWidth(int row){
        XSSFSheet sheet = (XSSFSheet) workbook.getSheetAt(0);
        int cellCount = getColumnCount(0,row);
        for (int i = 0; i <cellCount ; i++) {
            Cell cell = sheet.getRow(row).getCell(i);
            int colLength = cell.getStringCellValue().getBytes().length*300;
            sheet.setColumnWidth(i, colLength);
        }
    }

    public void setCellStyle(Integer row,Integer cell){
        CellStyle style = workbook.createCellStyle();
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());// 设置背景色
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        if(cell != null){
            workbook.getSheetAt(0).getRow(row).getCell(cell).setCellStyle(style);
        }else{
            XSSFSheet sheet = (XSSFSheet) workbook.getSheetAt(0);
            int cellCount = getColumnCount(0,row);
            for (int i = 0; i <cellCount ; i++) {
                Cell cell1 = sheet.getRow(row).getCell(i);
                cell1.setCellStyle(style);
            }
        }
    }

    public static String exportExcel(Workbook workbook, String fileName, FileConfig fileConfig) throws Exception{
        QrCodeUtils.mkdirs(fileConfig.getProfileSimple() + "/generatorExcel/");
        FileOutputStream os = new FileOutputStream(new File(fileConfig.getProfileSimple()+ "/generatorExcel/"+fileName+".xlsx"));
        workbook.write(os);
        os.flush();
        os.close();
        return fileConfig.getPre() + "/generatorExcel/"+fileName+".xlsx";
    }


    public List<String> readPic(FileConfig fileConfig,String path) throws IOException {
        String filePath = fileConfig.getProfileSimple() + path;
        QrCodeUtils.mkdirs(filePath);
        List<String> paths = new ArrayList<>();
        List<?> pictures = workbook.getAllPictures();
        if (pictures != null && !pictures.isEmpty()) {
            for (int i = 0; i < pictures.size(); i++) {
                String fileName = (new Date()).getTime()+".jpg";
                PictureData picture = (PictureData) pictures.get(i);
                byte[] data = picture.getData();
                FileOutputStream out = new FileOutputStream(filePath + fileName);
                out.write(data);
                out.close();
                String url = fileConfig.getPre()+ path + fileName;
                paths.add(url);
            }
        }
        return paths;
    }

}